Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organising data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of “electronic spreadsheets.” Better known simply as “spreadsheets,” these software programs provide a computerised replacement for the traditional financial modelling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator for a user. Because this “pad” exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, a letter to each column, and another letter to each sheet (or page) of the spreadsheet. To reference a location at column A and row 1 of the second page (i.e., the upper-left hand corner), for example, the user types in “B:A1”. In this manner, the spreadsheet defines an addressable storage location or “cell” at each intersection of a row with a column within a given page.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or “formulas” specifying calculations to be performed on the numbers stored in spreadsheet cells. Such spreadsheet cells can also be defined and named as a range as long as they are arranged as a convex set of cells. A typical example of such a named range simply corresponds to a regular table found in an accountant's pad. In this fashion, range names can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-OsbomeIMcGraw-Mll, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que corp., 1985. The disclosures of each of the foregoing are hereby incorporated by reference.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform “what-if” scenarios. After a set of computational relationships has been entered into a worksheet, thanks to imbedded formulas for instance, the information can be recalculated using different sets of assumptions. The result of each recalculation appears almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheet systems were invented to solve “what-if’ problems, that is, changing an input and seeing what happens to an output.
“What-if” problems can be formally represented by the definition of one or several user-defined options, each of them representing an assumption which can either be set as “TRUE” or “FALSE”. The effect of a single given user defined option can take different forms and requires that the spreadsheet user formally represents this effect thanks to different spreadsheet built-in means. With current spreadsheet technology, such spreadsheet means can be based on the writing of spreadsheet formulas (requiring thus some in-depth knowledge of the formula language and syntax), or can also be based on the utilisation of so-called versions. In both cases, there are several limitations which can turn these spreadsheet means into inefficient and error-prone solutions.
When relying on spreadsheet formulas, the user needs first to master the spreadsheet formula language, something which is by far not an easy task for somebody not used to programming languages. Then the user must define by himself some formal representation of the user-defined options, with the associated means for managing them: this second task is even more difficult as the user cannot rely on any stringent set of rules (as the ones implemented in a language compiler or interpreter) to determine if his work is error-free. Furthermore an electronic spreadsheet prepared by a given user with his or her own way of representing options will be difficult to be used by another user if the latter has not received precise instructions from the former on the way to handle the options. In short, unless mastering advanced programming skills, it is virtually impossible for a regular spreadsheet user to realise and share error-free “what-if” scenario thanks to user-defined options, by solely relying on the spreadsheet built-in formula language.
Current spreadsheet tools implement today the concept of versions and version groups, which represent some advantages with respect to the previous approach. Nevertheless using versions presents also some limitations, as outlined hereafter.
Let first recall the concept of versions, according to the following description found in the on-line help of the 1-2-3 spreadsheet tool from Lotus Corporation. “Versions are sets of different data for the same named range. Each version has a name, a date and time of creation and modification, and the name of the person who created or last modified the version. You can also assign styles and protection settings to a version and attach a comment. For example, you can name a range Revenues and create three versions of the range: HighRev, with values of 600, 500, 400, and 300; MedRev, with values of 500, 400, 300, and 200; and LowRev, with values of 400, 300, 200, and 100. You can create versions of any named range. For example, as well as creating versions of Revenues, you might name another range Expenses and create versions named HighExp, MedExp, and LowExp. When you create versions for a named range, all the versions are stored in the cells of the range. 1-2-3 calculates using the values in the currently displayed version. Any style or data changes you make to cells update the version within that range automatically.”
Once a range of cell is versionned, the user can defined several versions for this range. In the classical case where multiple options must be managed, the number of versions to be defined may become excessive. Indeed if an electronic spreadsheet must address a set of N independent options, any cell whose content depends on these N options should be represented with 2N versions, each of them corresponding to a given combination of these N options. Besides the resulting increase in file and memory storage (leading to degraded performances), this situation may become almost unmanageable for the user, specially in the case where multiple dispersed cells are versionned, even with the concept of version groups allowing to associate versions on different ranges of cells.
Thanks to a concept of Option_Manager and to a concept of Option_Applicator, it is possible to alleviate the above mentioned limitations and to easily define, manage and apply options within spreadsheets. The user first defines the user-defined options he/she needs for his/her own needs, then the user can apply one or several of these user-defined options to one or several given spreadsheet cells. By managing later on the different user-defined options introduced in the spreadsheet (in other words, by changing the user-defined options between the ‘TRUE’ and ‘FALSE’ values), the user can easily and instantaneously understand the impact of each user-defined option on the various cells.
If afterwards the user wishes to copy and paste or to cut and paste a source range of cells from the current spreadsheet to a destination range of cells, a problem may arise if the user-defined options defined in the source environment are not defined in the destination environment. For instance, this situation can happen if the destination range of cells and the source range of cells belong to a different file. In this a case, the dependency on the user-defined options is lost and the conventional spreadsheet tools realising the copy and paste or cut and paste operation can either end up with erroneous results, or with partial results. Indeed if the copy and paste or cut and paste operation is done “by value”, then each destination cell within the destination range of cells will receive the current value of the corresponding cell within the source range of cells, but all the information carried by the user-defined options will be lost. Indeed the formula translating how the user-defined options impact the content of a source cell is just replaced within the corresponding destination cell by the value it takes (with the current set of values of the user-defined options), so that any later modification of the value of any user-defined option will no longer impact the content of the destination cell. Alternatively, if the copy and paste or cut and paste operation is simply done by content, then the cell within the destination range of cells will show an ‘ERR’ result as its content points to user-defined options which cannot be resolved as soon as the context of the source range of cells becomes invisible from the context of the destination range of cells. The present invention resolves this problem by allowing the spreadsheet user to preserve, within the destination range of cells, the dependency on the user-defined options even if the destination and the source range of cells do not belong to the same context (for instance if they are not within the same spreadsheet file).